﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using ChinaDynastyTours.Models;
using System.Data.Common;

namespace ChinaDynastyTours.Controls
{
    class PayDetailCtrl
    {
        #region 付款详情信息查询

        /// <summary>
        /// 根据费用ID获取付款详情信息
        /// </summary>
        /// <param name="expenseId"></param>
        /// <returns></returns>
        internal static DataTable GetPayDetailDataByExpenseId(string expenseId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;


            string Sql = string.Format(@"SELECT *  FROM  vw_PayDetail WHERE ExpenseID={0} AND Status<>'{1}'", expenseId, ExpenseStatusEnum.取消);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }  
        /// <summary>
        /// 根据付款申请单ID获取付款详情信息
        /// </summary>
        /// <param name="expenseId"></param>
        /// <returns></returns>
        internal static DataTable GetPayDetailDataByPayApplyId(string payApplyId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT *  FROM  vw_PayDetail WHERE ItemId={0}",payApplyId);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }


        /// <summary>
        /// 根据供应商ID获取未付详情信息
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetUnPaidExpenseByProviderID(string providerId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT PayApplyID,PayDetailID,TourgroupID,TourgroupName,Budget,p.ApplyPay,p.ExpenseID  FROM  fin_PayDetail p,fee_Expense e WHERE ProviderID={0} and p.PayStatus='{1}' and p.ExpenseID = e.ExpenseID order by PayApplyID,TourgroupID", providerId, PayDetailStatusEnum.未付);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        ///// <summary>
        ///// 为月结提供的费用数据查询
        ///// </summary>
        ///// <param name="expenseId"></param>
        ///// <returns></returns>
        //internal static DataTable GetMonthPayDetailData(string tourGroupId, string providerId, DateTime startTime, DateTime endTime)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    DbCommand dbCommand;

        //    string szCondition = string.Format(" Where (StartDate>'{0}' AND StartDate<'{1}')", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"));

        //    if (tourGroupId != "")
        //        szCondition = " AND TourGroupID='" + tourGroupId + "'";
        //    if (szCondition != "")
        //    {
        //        if (providerId != "")
        //            szCondition += " AND ProviderID=" + providerId;
        //    }
        //    szCondition += string.Format(" AND PayStatus='{0}'", PayApplyStatusEnum.月结);
            
        //    string Sql = @"SELECT *  FROM  vw_MonthPayDetail ";

        //    Sql += szCondition;

        //    dbCommand = db.GetSqlStringCommand(Sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}         
        #endregion

        #region 付款详情信息增、删、改

        /// <summary>
        /// 更新付款详情和费用表
        /// </summary>
        /// <param name="applyPaysAndPayDetailIds"></param>
        /// <returns></returns>
        internal static int UpdatePayDetailAndExpense(Dictionary<int, Dictionary<int, decimal>> applyPaysAndPayDetailIds)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder sql = new StringBuilder();

            foreach (KeyValuePair<int, Dictionary<int, decimal>> item in applyPaysAndPayDetailIds)// PayDetailID是item.key  ExpenseID是item.Value.First().Key  ApplyPay 是 item.Value.First().Value
            {
                //在更新前，先据付款详情表中原有‘本次申请额’撤销费用表中的此次申请（费用表 ApplyPayTota = ApplyPayTotal - 付款详情表中的‘本次申请额’）
                sql.AppendFormat("UPDATE fee_expense SET ApplyPayTotal=ApplyPayTotal -(SELECT ApplyPay FROM fin_payDetail WHERE PayDetailID={0}) WHERE expenseId={1};", item.Key,item.Value.First().Key);

                //当上述费用表恢复至原申请前的状态后，将此次修改后的付款详情进行更新
                sql.AppendFormat(@"UPDATE fin_PayDetail SET [ApplyPay]={0} WHERE PayDetailID={1}; ", item.Value.First().Value, item.Key);

                //将此次修改后的付款详情（本次申请额）在费用表中做付款申请（已申请总额）及付款确认(已支付总额、费用支付状态)处理
                sql.AppendFormat(@"UPDATE fee_expense  SET PayTotal=PayTotal+{0}, ApplyPayTotal=ApplyPayTotal+{0},[PayStatus]='{1}' WHERE ExpenseID={2}; ", item.Value.First().Value, PayApplyStatusEnum.已付,item.Value.First().Key);
            }

            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }
        /// <summary>
        /// 更新付款詳情單中實際付款額
        /// </summary>
        /// <param name="payDetailId"></param>
        /// <param name="realPayment"></param>
        /// <returns></returns>
        //internal static int UpdateRealPaymentForPayDetail(string payDetailId, decimal realPayment)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"UPDATE fin_PayDetail SET [RealPayment]={0} WHERE PayDetailID={1}", realPayment,payDetailId);

        //    return db.ExecuteNonQuery(CommandType.Text, sql);
        //}
        
        //internal static int DeletePayDetail(string payApplyId)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"DELETE FROM fin_PayDetail WHERE PayApplyID={0}", payApplyId);
        //    return db.ExecuteNonQuery(CommandType.Text, sql);
        //}

        #endregion
        
        /// <summary>
        /// 补充摘要
        /// </summary>
        /// <param name="remark"></param>
        /// <param name="payDetailId"></param>
        /// <returns></returns>
        internal static int UpdatePayDetailRemark(string remark, string payDetailId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_PayDetail SET [Remark]='{0}' WHERE PayDetailID={1}", remark, payDetailId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

    }


}
